﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public class RequirenmentAnalysisService
    {

        public int HaveTab( string TabNam ) {
            string sql = string.Format("select count(1) from RequirenmentAnalysis{0} ", TabNam);
            try {
                SqlConnection conn = ConnPoll.GetConn();
                return Convert.ToInt32(SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql));
            } catch (Exception) {
                return -1;
            }
        }

        #region 创建需求分析表
        /// <summary>
        /// 创建需求分析表
        /// </summary>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public void CreateRequirenmentAnalysis(string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            try
            {
                ConnPoll.Open();
                string tbna = "RequirenmentAnalysis" + UserName;
                string sql = " use bds249611391_db "
                    + " create table " + tbna
                    + " ( "
                    + " RequirenmentId int not null identity(1,1),"//主键Id
                    + " FriendName nvarchar(2000) not null,"//现阶段需求
                    + " CurrentDemand nvarchar(2000) not null,"//现阶段需求
                    + " PotentialDemand nvarchar(2000) not null,"//潜在需求
                    + " Custom1 nvarchar(2000) null,"//自定义项可空
                    + " Custom2 nvarchar(2000) null,"//自定义项可空
                    + " Custom3 nvarchar(2000) null"//自定义项可空
                    + " ) "
                    + " use bds249611391_db "
                    + " alter table " + tbna
                    + " add constraint PK_RequirenmentId primary key (RequirenmentId)";
                SqlCommand com = new SqlCommand(sql, conn);
                com.ExecuteNonQuery();
                com = null;
                ConnPoll.Close();
            }
            catch (Exception)
            {

            }
        }
        #endregion

        #region 新增信息
        /// <summary>
        /// 新增信息
        /// </summary>
        /// <param name="ra"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Insert(RequirenmentAnalysis ra,string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "Insert into RequirenmentAnalysis"+ UserName + " (FriendName,CurrentDemand,PotentialDemand,Custom1,Custom2,Custom3) "
                + " values (@FriendName,@CurrentDemand,@PotentialDemand,@Custom1,@Custom2,@Custom3)";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@FriendName",ra.FriendName),
                new SqlParameter("@CurrentDemand",ra.CurrentDemand),
                new SqlParameter("@PotentialDemand",ra.PotentialDemand),
                new SqlParameter("@Custom1",ra.Custom1),
                new SqlParameter("@Custom2",ra.Custom2),
                new SqlParameter("@Custom3",ra.Custom3)
            };
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text,sql,pars);
        }
        #endregion

        #region 删除信息
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="ra"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Delete(RequirenmentAnalysis ra, string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "delete RequirenmentAnalysis" + UserName + " where RequirenmentId=@RequirenmentId";
            return SQLHelper.ExecuteNonQuery(conn,System.Data.CommandType.Text,sql,new SqlParameter("@RequirenmentId",ra.RequirenmentId));
        }
        #endregion

        #region 修改信息
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="ra"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Set(RequirenmentAnalysis ra, string UserName,string FriendName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "update RequirenmentAnalysis" + UserName
                + " set "
                + " CurrentDemand=@CurrentDemand,"
                + " PotentialDemand=@PotentialDemand,"
                + " Custom1=@Custom1,"
                + " Custom2=@Custom2,"
                + " Custom3=@Custom3"
                + " where FriendName=@FriendNames";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@CurrentDemand",ra.CurrentDemand),
                new SqlParameter("@PotentialDemand",ra.PotentialDemand),
                new SqlParameter("@Custom1",ra.Custom1),
                new SqlParameter("@Custom2",ra.Custom2),
                new SqlParameter("@Custom3",ra.Custom3),
                new SqlParameter("@FriendNames",FriendName)
            };
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text,sql,pars);
        }
        #endregion

        #region 查询信息
        /// <summary>
        /// 查询信息
        /// </summary>
        /// <param name="ra"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int SearchAll( string UserName,string FriendName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "select count(1) from RequirenmentAnalysis" + UserName + " where FriendName = '" + FriendName+"'";
            try
            {
                return Convert.ToInt32(SQLHelper.ExecuteScalar(conn,System.Data.CommandType.Text,sql));
            }
            catch (Exception)
            {
                return -1;
                throw;
            }
            
        }
        #endregion

        public RequirenmentAnalysis Show(string UserName, string FriendName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = string.Format("select * from RequirenmentAnalysis{0} where FriendName = '{1}'", UserName, FriendName);
            SqlDataReader dr = SQLHelper.ExecuteReader(conn,System.Data.CommandType.Text,sql);
            RequirenmentAnalysis ra = new RequirenmentAnalysis();
            while(dr.Read())
            {
                ra.RequirenmentId = Convert.ToInt32(dr["RequirenmentId"]);
                ra.CurrentDemand = dr["CurrentDemand"].ToString().Trim();
                ra.PotentialDemand = dr["PotentialDemand"].ToString().Trim();
                ra.Custom1 = dr["Custom1"].ToString().Trim();
                ra.Custom2 = dr["Custom2"].ToString().Trim();
                ra.Custom3 = dr["Custom3"].ToString().Trim();
            }
            dr.Close();
            return ra;
        }
    }
}
